﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using Skyworth.CRM.Service.IDAL;

namespace Skyworth.CRM.DAL
{
    public class LetterOfAdviceDAL : SuperDataBase, ILetterOfAdviceDAL
    {
       public DataTable GetLetterOfAdviceByPage(string strWhere, string strOrderBy, int pageIndex, int pageSize, out int recordCount, out int pageCount)
       {
           recordCount = 0;
           pageCount = 0;
           StringBuilder strCommandText = new StringBuilder();
           DataSet ds;
           try
           {
               strCommandText.AppendFormat(
              "SELECT row_number() over({0}) AS rownum,Fitemid,FCustid,Fdate,FCyID,Fnumber,FEmpName,FBuyDept,FDept,Fsalse", strOrderBy);
               strCommandText.Append(" FROM T_Sales_SEOutStock ");
               strCommandText.Append(strWhere);

               System.Data.Common.DbCommand sqlcommand = DB.GetStoredProcCommand("sp_GetPager");
               DB.AddInParameter(sqlcommand, "@Psql", DbType.String, strCommandText.ToString());
               DB.AddInParameter(sqlcommand, "@PNum", DbType.Int32, pageIndex);
               DB.AddInParameter(sqlcommand, "@PSize", DbType.Int32, pageSize);
               DB.AddInParameter(sqlcommand, "@orderBy", DbType.String, "");
               DB.AddInParameter(sqlcommand, "@RowNumName", DbType.String, "rownum");
               DB.AddOutParameter(sqlcommand, "@pTotalRecords", DbType.Int32, 4);
               DB.AddOutParameter(sqlcommand, "@pTotalPages", DbType.Int32, 4);
               ds = DB.ExecuteDataSet(sqlcommand);
               recordCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalRecords"));
               pageCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalPages"));
           }
           catch (Exception)
           {

               throw;
           }
           return ds.Tables[0];
       }

       public DataTable GetDetailedLetterOfAdviceByPage(string strWhere, string strOrderBy, int pageIndex, int pageSize, out int recordCount, out int pageCount)
       {
           recordCount = 0;
           pageCount = 0;
           StringBuilder strCommandText = new StringBuilder();
           DataSet ds;
           try
           {
               strCommandText.AppendFormat(
              "SELECT row_number() over({0}) AS rownum,Fitemid,FSaleStockID,FOrderID,FOrdernumber,FDate,Fproductid,FProductnumber,FProductname,Funit,FQTy,FPrice,FAmount,FisR", strOrderBy);
               strCommandText.Append(" FROM T_Sales_SEOutStockEntry ");
               strCommandText.Append(strWhere);

               System.Data.Common.DbCommand sqlcommand = DB.GetStoredProcCommand("sp_GetPager");
               DB.AddInParameter(sqlcommand, "@Psql", DbType.String, strCommandText.ToString());
               DB.AddInParameter(sqlcommand, "@PNum", DbType.Int32, pageIndex);
               DB.AddInParameter(sqlcommand, "@PSize", DbType.Int32, pageSize);
               DB.AddInParameter(sqlcommand, "@orderBy", DbType.String, "");
               DB.AddInParameter(sqlcommand, "@RowNumName", DbType.String, "rownum");
               DB.AddOutParameter(sqlcommand, "@pTotalRecords", DbType.Int32, 4);
               DB.AddOutParameter(sqlcommand, "@pTotalPages", DbType.Int32, 4);
               ds = DB.ExecuteDataSet(sqlcommand);
               recordCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalRecords"));
               pageCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalPages"));

               
           }
           catch (Exception)
           {

               throw;
           }
           return ds.Tables[0];
       }

        public string Export2Excel(string strWhere)
        {
            StringBuilder strCommandText = new StringBuilder();
            StringBuilder returnValue=new StringBuilder( );
            IDataReader dataReader;
            try
            {
                strCommandText.AppendFormat(
               "SELECT a.*,b.* from (select * from T_Sales_SEOutStock {0}) as a left join T_Sales_SEOutStockEntry AS b ON a.Fitemid = b.FSaleStockID ORDER BY a.Fitemid FOR XML AUTO, root('Root')",strWhere);


                DbCommand sqlcommand = DB.GetSqlStringCommand(strCommandText.ToString());
                dataReader = DB.ExecuteReader(sqlcommand);
               
                while (dataReader.Read())
                {
                    returnValue.Append(dataReader[0].ToString());
                }
                dataReader.Close();
            }
            catch (Exception)
            {
                throw;
            }
            return returnValue.ToString();
        }
    }
}
